package com.dudu.dao;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.dudu.bean.ParamBean;
import com.dudu.model.ChargingRule;
import com.dudu.model.RuleAccount;

import core.CoreDao;
import core.db.DBConnectionUtil;

/**
 * 能力相关配置
 * 
 * @author 危东
 * @date 2016-4-26
 */
public class CharginRuleDao {
    private static final Logger log = core.util.Logger.getLogger(CharginRuleDao.class.getSimpleName());
    /**
     * 插入能力规则信息数据(t_charging_rule)
     * 
     * @param dao
     * @param bean
     * @return
     */
    public int insertChargingRule(CoreDao dao, ChargingRule bean) {

	Connection con = null;
	int result = -1;
	try {
	    con = DBConnectionUtil.getDBConnection("emate1");
	    StringBuffer sql = new StringBuffer(
		    "INSERT INTO t_charging_rule(parent_id, menu_type, default_using,names,  begin_time, end_time, type, status, create_time, comments)");
	    sql.append("VALUES(?,?,?,?, ?, ?, ?, ?, sysdate(), ?)");

	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
		    new Object[] { bean.getParent_id(), bean.getMenu_type(), bean.getDefault_using(), bean.getNames(), bean.getBegin_time(), bean.getEnd_time(), bean.getType(),
			    bean.getStatus(), bean.getComments() });

	    JSONObject obj = list.get(0);
	    result = obj.optInt("result");

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 编辑能力规则(t_charging_rule)
     * 
     * @param dao
     * @param bean
     * @return
     */
    public int updateChargingRule(CoreDao dao, ChargingRule bean) {

	Connection con = null;
	int result = -1;

	try {
	    con = DBConnectionUtil.getDBConnection("emate1");
	    StringBuffer sql = new StringBuffer(
		    "UPDATE t_charging_rule set parent_id=?, menu_type=?, names=?,  begin_time=?, end_time=?, type=?, status=?, create_time=sysdate(), comments=?");
	    sql.append(" where id=?");

	    List<JSONObject> list = dao.serviceJsonSql(
		    con,
		    sql.toString(),
		    new Object[] { bean.getParent_id(), bean.getMenu_type(), bean.getNames(), bean.getBegin_time(), bean.getEnd_time(), bean.getType(), bean.getStatus(),
			    bean.getComments(), bean.getId() }

	    );

	    if (list != null && list.size() > 0) {
		list.get(0);
		JSONObject obj = list.get(0);
		result = obj.optInt("result");
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }
    /**
     * 根据能力规则数据库id查询数据(t_charging_rule)
     * 
     * @param dao
     * @param id
     * @return
     */
    public ChargingRule query(CoreDao dao, String id) {

	Connection con = null;
	ChargingRule result = null;

	try {
	    con = DBConnectionUtil.getDBConnection("emate1");

	    StringBuffer sql = new StringBuffer("SELECT * FROM t_charging_rule WHERE id=?");
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { id });
	    if (list != null && list.size() > 0) {
		List<ChargingRule> items = dao.covertToBean(list, ChargingRule.class);
		result = items.get(0);
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 根据能力类型查询数据(t_charging_rule)
     * 
     * @param dao
     * @param type
     * @return
     */
    public ChargingRule queryByType(CoreDao dao, String type) {

	Connection con = null;
	ChargingRule result = null;

	try {
	    con = DBConnectionUtil.getDBConnection("emate1");

	    StringBuffer sql = new StringBuffer("SELECT * FROM t_charging_rule WHERE  binary type=? ");
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { type });
	    if (list != null && list.size() > 0) {
		List<ChargingRule> items = dao.covertToBean(list, ChargingRule.class);
		result = items.get(0);
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 查询父节点信息列表(t_charging_rule)
     * 
     * @param dao
     * @return
     */
    public List<ParamBean> queryAllParentId(CoreDao dao) {
	log.info("CharginRuleDao.queryAllParentId");
	// 数据库连接
	Connection con = null;
	List<ParamBean> result = null;

	try {
	    // 获取数据库连接
	    con = DBConnectionUtil.getDBConnection("emate1");
	    // 拼写sql
	    StringBuffer sql = new StringBuffer("select id, names as text FROM t_charging_rule WHERE menu_type='types' AND parent_id='-1'");
	    log.info("sql:" + sql);
	    // 查询相应信息
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
	    if (list != null && list.size() > 0) {
		result = dao.covertToBean(list, ParamBean.class);
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 功能：根据parent_id查询父节点信息列表(t_charging_rule)
     * 
     * @param dao
     * @param parent_id
     * @return
     */
    public List<ChargingRule> queryByParentId(CoreDao dao, String parent_id) {
	log.info("CharginRuleDao.queryByParentId");
	Connection con = null;
	List<ChargingRule> result = null;
	try {
	    con = DBConnectionUtil.getDBConnection("emate1");
	    StringBuffer sql = new StringBuffer("select * FROM t_charging_rule where parent_id=? ");
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { parent_id });
	    if (list != null && list.size() > 0) {
		List<ChargingRule> items = dao.covertToBean(list, ChargingRule.class);
		result = items;
	    }
	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}

	return result;

    }

    /**
     * 根据父级规则类型查询相应子集的默认能力配置（t_charging_rule）
     * 
     * @param dao
     * @param abilityType
     *            (父节点type:MPTY,SMMS等 )
     * @return
     */
    public ChargingRule queryRuleByParentType(CoreDao dao, String abilityType) {
	Connection con = null;
	// 查询结果
	List<JSONObject> list = null;
	// 查询的list
	List<ChargingRule> result = null;
	// 最后返回的值
	ChargingRule chargingRule = null;
	try {
	    // 获取数据库连接
	    con = DBConnectionUtil.getDBConnection("emate1");
	    // 拼写查询sql
	    StringBuffer sql = new StringBuffer("SELECT tr2.* FROM t_charging_rule tr1,t_charging_rule tr2 WHERE tr2.default_using='yes' AND tr1.type=? AND tr1.id=tr2.parent_id ");

	    log.info("queryRuleByParentType=" + sql.toString() + abilityType);

	    // 获取查询值
	    list = dao.serviceJsonSql(con, sql.toString(), new Object[] { abilityType });

	    // 将json类型转化为account
	    if (list != null) {
		List<ChargingRule> items = dao.covertToBean(list, ChargingRule.class);
		result = items;
		if (result.size() > 0) {
		    chargingRule = result.get(0);
		}
	    }
	} catch (Exception ex) {
	    log.info("queryRuleByParentType bug=" + ex.getMessage());
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return chargingRule;
    }

    /**
     * 查询所有的规则(t_charging_rule)带分页
     * 
     * @param dao
     * @param pageSql
     * @param paramSql
     *            查询条件
     * @return
     */
    public List<ChargingRule> queryAllRule(CoreDao dao, String pageSql, String paramSql) {
	log.info("CharginRuleDao.queryAllRule");
	Connection con = null;
	// 查询结果
	List<JSONObject> list = new ArrayList<JSONObject>();
	// 最后需返回的
	List<ChargingRule> result = null;
	try {
	    // 获取数据库连接
	    con = DBConnectionUtil.getDBConnection("emate1");
	    // 拼写查询sql
	    StringBuffer sql = new StringBuffer("select * FROM t_charging_rule ");
	    // 判断如果查询条件不为空，则拼写sql
	    if (StringUtils.isNotEmpty(paramSql)) {
		sql.append(" WHERE ");
		sql.append(paramSql);
	    }

	    sql.append(pageSql);
	    log.info("queryAllRule sql=" + sql.toString());
	    // 获取查询值
	    list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});
	    // 将json类型转化为account
	    if (list != null) {
		List<ChargingRule> items = dao.covertToBean(list, ChargingRule.class);
		result = items;
	    }
	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 查询所有的规则数量(t_charging_rule)带分页
     * 
     * @param dao
     * @param paramSql
     * @return
     */
    public int getTotalRule(CoreDao dao, String paramSql) {
	log.info("CharginRuleDao.getTotalRule");
	if (StringUtils.isNotBlank(paramSql)) {
	    paramSql = " and " + paramSql;
	} else {
	    paramSql = "";
	}
	Connection con = null;
	int result = -1;
	try {
	    con = DBConnectionUtil.getDBConnection("emate1");
	    StringBuffer sql = new StringBuffer("select count(1) as totalRule from t_charging_rule tac where 1=1");
	    sql.append(paramSql);

	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});

	    JSONObject obj = list.get(0);
	    result = (Integer) obj.get("totalRule");
	    log.info("getTotalRule=" + sql.toString());

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 根据appKey与账号标识获取已配置规则关系数据（t_rule_account）
     * 
     * 如果custAccount值为-1，则为查询应用的配置
     * 
     * @author gaoxs
     * @param dao
     * @param appKey
     * @param custAccount
     * @return
     */
    public List<RuleAccount> queryRuleByAppAndAccount(CoreDao dao, String appKey, String custAccount) {
	log.info("CharginRuleDao.queryRuleByAppAndAccount");
	Connection con = null;
	// 查询结果
	List<JSONObject> list = new ArrayList<JSONObject>();
	// 最后需返回的
	List<RuleAccount> result = null;
	try {
	    // 获取数据库连接
	    con = DBConnectionUtil.getDBConnection("emate1");
	    // 拼写查询sql
	    StringBuffer sql = new StringBuffer("SELECT * FROM t_rule_account tra ");
	    sql.append("WHERE app_key=? AND cust_account=?");
	    log.info("queryRuleByAppAndAccount sql=" + sql.toString());
	    // 获取查询值
	    list = dao.serviceJsonSql(con, sql.toString(), new Object[] { appKey, custAccount });
	    // 将json类型转化为RuleAccount
	    if (list != null) {
		List<RuleAccount> items = dao.covertToBean(list, RuleAccount.class);
		result = items;
	    }
	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 查询计费账号的能力配置(appKey,cust_account,type=MPTY)
     * 
     * 如果custAccount值为-1，则为查询应用的配置
     * 
     * @param dao
     * @param appKey
     * @param custAccount
     * @param abilityType
     * @return
     */
    public ChargingRule queryDefAbilityByAccIdAndType(CoreDao dao, String appKey, String custAccount, String abilityType) {
	Connection con = null;
	// 查询结果
	List<JSONObject> list = new ArrayList<JSONObject>();
	// 最后需返回的
	ChargingRule result = null;
	try {
	    // 获取数据库连接
	    con = DBConnectionUtil.getDBConnection("emate1");
	    // 拼写查询sql
	    StringBuffer sql = new StringBuffer("SELECT tcr.id,tcr.names,tcr.type,tcr.parent_id,tcr.menu_type,tcr.status,tcr.create_time ");
	    sql.append(" FROM (SELECT tr2.* FROM t_charging_rule tr1,t_charging_rule tr2 WHERE tr1.type=? AND tr1.id=tr2.parent_id) tcr,t_rule_account tra ");
	    sql.append(" WHERE tra.rule_id=tcr.id AND tra.app_key=? AND tra.cust_account=? ");

	    // 获取查询值
	    list = dao.serviceJsonSql(con, sql.toString(), new Object[] { abilityType, appKey, custAccount });
	    // 将json类型转化为RuleAccount
	    if (list != null) {
		List<ChargingRule> items = dao.covertToBean(list, ChargingRule.class);
		if (items.size() > 0) {
		    result = items.get(0);
		}
	    }
	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 按appKey与账号标识或者规则ID删除规则(t_rule_account)
     * 
     * @param dao
     * @param app_key
     * @param cust_account
     * @return
     */
    public int deleteChargingRule(CoreDao dao, String app_key, String cust_account,String rule_id) {
	log.info("ChargingRuleDao.deleteByapp_key");
	Connection con = null;
	int result = -1;
	try {
	    con = DBConnectionUtil.getDBConnection("emate1");
	    StringBuffer sql = new StringBuffer("DELETE FROM t_rule_account WHERE app_key=? ");
	    if (StringUtils.isNotBlank(cust_account)) {
		sql.append("and cust_account='" + cust_account.trim() + "'");
	    }
	    if(StringUtils.isNotBlank(rule_id)){
		sql.append(" and rule_id="+rule_id);
	    }
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { app_key });
	    if (list != null && list.size() > 0) {
		list.get(0);
		JSONObject obj = list.get(0);
		result = obj.optInt("result");
	    }
	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 插入账号-能力规则关系数据(t_rule_account)
     * 
     * @param dao
     * @param bean
     * @return
     */
    public int insertRuleAccount(CoreDao dao, RuleAccount bean) {
	log.info("CharginRuleDao.insertRuleAccount");
	Connection con = null;
	int result = -1;
	try {
	    con = DBConnectionUtil.getDBConnection("emate1");
	    StringBuffer sql = new StringBuffer(
		    "INSERT INTO t_rule_account (app_key, cust_account, periphery_code, rule_id, account_type, rule_names,create_time) VALUES(?, ?, ?, ?, ?, ?, sysdate());");
	    log.info("CharginRuleDao.insertRuleAccount==========" + sql.toString());
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { bean.getApp_key(), bean.getCust_account(), bean.getPeriphery_code(), bean.getRule_id(),
		    bean.getAccount_type(), bean.getRule_names() });

	    JSONObject obj = list.get(0);
	    result = obj.optInt("result");

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * t_charging_rule
     * 若保留原来已经配置的应用账号与组织账号的“默认使用”，而只更改未配置的账号：
     * 编辑能力规则的默认使用，将parent_id相同的数据都把default_using='no'
     * 
     * ruleinfo.js
     * 
     * @param dao
     * @param bean
     * @return
     */
    public int updateByRadiobtnNo(CoreDao dao, ChargingRule bean) {
	log.info("CharginRuleDao.updateByRadiobtn");
	Connection con = null;
	int result = -1;
	try {
	    con = DBConnectionUtil.getDBConnection("emate1");
	    StringBuffer sql = new StringBuffer("UPDATE t_charging_rule SET default_using='no' WHERE id IN");
	    sql.append("(SELECT tcr.id FROM ");
	    sql.append("(SELECT id FROM t_charging_rule WHERE parent_id=");
	    sql.append("(SELECT parent_id FROM t_charging_rule WHERE id=?)");
	    sql.append(" ) tcr)");
	    log.info(sql);
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { bean.getId() });
	    if (list != null && list.size() > 0) {
		// list.get(0);
		JSONObject obj = list.get(0);
		result = obj.optInt("result");
	    }
	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }
    /**
     * t_charging_rule
     * 若保留原来已经配置的应用账号与组织账号的“默认使用”，而只更改未配置的账号：
     * 编辑能力规则的默认使用，将按钮选中的数据，把default_using='yes'
     * 
     * ruleinfo.js
     * 
     * @param dao
     * @param bean
     * @return
     */
    public int updateDefaultUsing(CoreDao dao, ChargingRule bean) {
	log.info("CharginRuleDao.updateDefaultUsing");
	Connection con = null;
	int result = -1;
	try {
	    con = DBConnectionUtil.getDBConnection("emate1");
	    StringBuffer sql = new StringBuffer("UPDATE t_charging_rule set default_using='yes'");
	    sql.append(" where id=?");
	    log.info(sql);
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { bean.getId() });
	    if (list != null && list.size() > 0) {
		list.get(0);
		JSONObject obj = list.get(0);
		result = obj.optInt("result");
	    }
	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }
}
